﻿#region Disclaimer/Info
///////////////////////////////////////////////////////////////////////////////////////////////////
// MetaCollection
// 
// MetaCollection is a system to provide a standard structure, permissions and metadata service
// for content organization.
//
// This project is hosted at Google Code at http://code.google.com/p/metacollection/
//
// This project is licensed under the BSD license.  See the License.txt file for more information.
///////////////////////////////////////////////////////////////////////////////////////////////////
#endregion

using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using Community.CsharpSqlite.SQLiteClient;
using MetaCollection.Framework;
using MetaCollection.Framework.Change;
using MetaCollection.Framework.Model;

namespace MetaCollection.SqliteRepository
{
	/// <summary>
	/// Implents an IItemRepository using a Sqlite database backing store.  If the given
	/// file in the connection string does not exist then the database will be created.
	/// </summary>
	public sealed class ItemRepository : MetaCollection.Framework.Repository.IItemRepository, IDisposable
	{
		private string m_ConnectionString;
		private bool m_Initialized;
		private SqliteConnection m_SqlConn;

		/// <summary>
		/// Ensures the repository has been initialized.
		/// </summary>
		/// <exception cref="InvalidOperationException">Thrown if <see cref="AdoNetItem.Initialize"/> has not been called.</exception>
		private void EnsureInitialized()
		{
			if (!m_Initialized)
				throw new InvalidOperationException("Repository must be initialized prior to use.");
		}

		#region IRepository Members

		/// <summary>
		/// Initializes a new instance of the IItemRepository
		/// </summary>
		/// <param name="configData"></param>
		public void Initialize(string configData)
		{
			if (string.IsNullOrEmpty(configData))
				throw new ArgumentNullException("configData");
			m_ConnectionString = ResolveRelativePath(configData);
			m_SqlConn = new SqliteConnection(m_ConnectionString);
			VerifyDatabase();
			m_Initialized = true;
		}

		/// <summary>
		/// Updates a relative path with a full path within the connection string.
		/// </summary>
		/// <param name="configData">a valid sqlite connection string</param>
		/// <returns>an updated connection string with an absolute path to the file location replacing a given relative path</returns>
		private static string ResolveRelativePath(string configData)
		{
			Func<string, string> PathResolve = (System.Web.HttpContext.Current != null ? (Func<string, string>)System.Web.HttpContext.Current.Server.MapPath : Path.GetFullPath);
			var connectionParams = configData.Split(',');
			for (var i=0; i < connectionParams.Length; i++)
			{
				if (connectionParams[i].StartsWith("uri=file:", StringComparison.OrdinalIgnoreCase))
					connectionParams[i] = String.Format(CultureInfo.InvariantCulture, "uri=file:{0}", PathResolve(connectionParams[i].Substring(9)));
			}
			return string.Join(",", connectionParams);
		}

		/// <summary>
		/// Checks through the tables in the database and has any that are missing created.
		/// </summary>
		private void VerifyDatabase()
		{
			var tableNames = Resources.ItemDatabaseTables;
			m_SqlConn.Open();
			using (SqliteCommand cmd = new SqliteCommand("SELECT tbl_name FROM Sqlite_master WHERE type = 'table'  ORDER BY name", m_SqlConn))
			{
				var reader = cmd.ExecuteReader();
				var ordinal = reader.GetOrdinal("tbl_name");
				while (reader.Read())
				{
					tableNames = tableNames.Replace(reader.GetString(ordinal) + ",", "");
				}
				if (tableNames.Trim().Length > 0)
					throw new InvalidOperationException(String.Format(CultureInfo.CurrentCulture, Resources.ErrorMissingRequiredTable, Resources.ItemDatabaseTables));
			}
			m_SqlConn.Close();
		}

		#endregion

		#region IItemRepository Members

		/// <summary>
		/// Gets all versions of an item
		/// </summary>
		/// <param name="id">The id of the item.</param>
		/// <param name="opt">Load Options for what data to return</param>
		/// <returns>
		/// An ItemInstance for the given Item Id or null if no object found
		/// </returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and a method which generates static sql and is parameterized.")]
		public IEnumerable<Framework.Model.IItemInstance> GetItem(Guid id, Framework.Model.LoadOptions opt)
		{
			EnsureInitialized();
			using (SqliteConnection connection = new SqliteConnection(m_ConnectionString))
			{
				using (var command = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, Resources.ItemGetSql, GetSelectColumns(opt)), connection))
				{
					command.Parameters.Add("@id", id.ToString().ToUpperInvariant());
					return GetItemsFromDatabase(command, opt);
				}
			}
		}

		/// <summary>
		/// Finds an Item by Id-Version.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <param name="version">The version.</param>
		/// <param name="opt">Load Options for what data to return</param>
		/// <returns>
		/// An ItemInstance for the given Item Id-Version or null if no object found
		/// </returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and a method which generates static sql and is parameterized.")]
		public Framework.Model.IItemInstance GetItem(Guid id, int version, Framework.Model.LoadOptions opt)
		{
			EnsureInitialized();
			using (SqliteConnection connection = new SqliteConnection(m_ConnectionString))
			{
				using (var command = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, Resources.ItemGetVersionSql, GetSelectColumns(opt)), connection))
				{
					command.Parameters.Add("@id", id.ToString().ToUpperInvariant());
					command.Parameters.Add("@version", version);
					return GetItemsFromDatabase(command, opt).FirstOrDefault();
				}
			}
		}

		/// <summary>
		/// Puts the item into persistant storage or updates an existing item.
		/// </summary>
		/// <param name="item">item to persist</param>
		/// <param name="changeCollection">changes on the item that should be persisted.</param>
		/// <param name="requestType">Type of request associated with this save item operation</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1002:DoNotExposeGenericLists", Justification="Must use the change collection per the interface spec")]
		public Framework.Model.IItemInstance SaveItem(Framework.Model.IItemInstance item, Framework.Change.ItemChangeCollection changeCollection) //, Framework.RequestTypes requestType)
		{
			EnsureInitialized();

			if (item == null)
				throw new ArgumentNullException("item", "Item cannot be null.");

			if (changeCollection == null)
				throw new ArgumentNullException("changeCollection", "Change Collection cannot be null.");

			//if (!requestType.HasType(RequestTypes.SetItem))
			//     throw new ArgumentException("Save must contain request type of setitem.", "requestType");

			if (changeCollection.ChangeType.HasType(ItemChangeTypes.Create))
				CreateItem(item, changeCollection.Options);
			else if (changeCollection.Count > 0)
			{
				UpdateItemInstance(item, changeCollection);				
			}
			return item;
		}

		/// <summary>
		/// Removes the item with the specified Id
		/// </summary>
		/// <param name="id">Id of the item to remove</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and is parameterized.")]
		public void RemoveItem(Guid id)
		{
			EnsureInitialized();
			using (SqliteConnection connection = new SqliteConnection(m_ConnectionString))
			{
				connection.Open();
				using (var trans = connection.BeginTransaction())
				{
					using (var command = connection.CreateCommand())
					{
						command.CommandText = Resources.ItemDeleteItemSql;
						command.Parameters.Add("@id", id.ToString().ToUpperInvariant());
						command.ExecuteNonQuery();
					}
					trans.Commit();
				}
			}
		}

		/// <summary>
		/// Removes the specified Item Version and additionally the entire item if no other versions remain
		/// </summary>
		/// <param name="id">id of the item to remove</param>
		/// <param name="version">version of the item to remove</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification="SQL comes from a resource file and is parameterized.")]
		public void RemoveItem(Guid id, int version)
		{
			EnsureInitialized();
			using (SqliteConnection connection = new SqliteConnection(m_ConnectionString))
			{
				connection.Open();
				using (var trans = connection.BeginTransaction())
				{
					using (var command = connection.CreateCommand())
					{
						command.CommandText = Resources.ItemDeleteItemVersionSql;
						command.Parameters.Add("@id", id.ToString().ToUpperInvariant());
						command.Parameters.Add("@version", version);
						command.ExecuteNonQuery();
					}
					trans.Commit();
				}
			}
			if (!GetItem(id, LoadOptions.ItemReference).Any()) // if there are no versions left then make sure we fully clean up house
				RemoveItem(id);
		}

		/// <summary>
		/// Finds an item by name with a given parent.
		/// </summary>
		/// <param name="parentId">The parent id.</param>
		/// <param name="name">The name.</param>
		/// <param name="opt">Load Options for what data to return</param>
		/// <returns>an ItemInstance</returns>
		/// <remarks>Returns the published version of each item</remarks>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and a method which generates static sql and is parameterized.")]
		public IEnumerable<Framework.Model.IItemInstance> FindByName(Guid parentId, string name, Framework.Model.LoadOptions opt)
		{
			EnsureInitialized();
			using (SqliteConnection connection = new SqliteConnection(m_ConnectionString))
			{
				using (var command = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, Resources.ItemFindByNameSql, GetSelectColumns(opt)), connection))
				{
					command.Parameters.Add("@parentId", parentId.ToString().ToUpperInvariant());
					command.Parameters.Add("@name", name);
					return GetItemsFromDatabase(command, opt);
				}
			}
		}

		/// <summary>
		/// Finds all the items with a given parent.
		/// </summary>
		/// <param name="parentId">The parent id.</param>
		/// <param name="includeIndirect">if set to <c>true</c> the children that are not directly under the parent are also included.</param>
		/// <param name="opt">Load Options for what data to return</param>
		/// <returns></returns>
		/// <remarks>Returns the published version of each item</remarks>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and a method which generates static sql and is parameterized.")]
		public IEnumerable<Framework.Model.IItemInstance> FindByParent(Guid parentId, bool includeIndirect, Framework.Model.LoadOptions opt)
		{
			EnsureInitialized();
			using (SqliteConnection connection = new SqliteConnection(m_ConnectionString))
			{
				connection.Open();
				if (includeIndirect)
				{
					using (var command = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, Resources.ItemFindByParentHrefSql, GetSelectColumns(opt)), connection))
					{

						command.Parameters.Add("@href", GetItemHref(parentId, connection));
						return GetItemsFromDatabase(command, opt);
					}
				}
				else
					using (var command = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, Resources.ItemFindByParentSql, GetSelectColumns(opt)), connection))
					{
						command.Parameters.Add("@parentId", parentId.ToString().ToUpperInvariant());
						return GetItemsFromDatabase(command, opt);
					}
			}
		}

		#endregion

		/// <summary>
		/// Creates the item.
		/// </summary>
		/// <param name="item">The item.</param>
		private void CreateItem(IItemInstance item, LoadOptions opt)
		{
			using (var connection = new SqliteConnection(m_ConnectionString))
			{
				connection.Open();
				using (var trans = connection.BeginTransaction())
				{
					try
					{
						if (item.ParentId == null || item.ParentId == Guid.Empty)
						{
							InsertItemClaim(item, opt, connection);
						}

						InsertItem(item, opt, connection);
						item.Version = InsertItemVersion(item, opt, connection);
						InsertItemRelationships(item, opt, connection);
						trans.Commit();
					}
					catch (Exception)
					{
						trans.Rollback();
						throw;
					}
				}
			}
		}

		/// <summary>
		/// Inserts the item.
		/// </summary>
		/// <remarks>The connection given should have a current transaction in place.</remarks>
		/// <param name="item">The item.</param>
		/// <param name="opt">The options of which fields should be set.</param>
		/// <param name="connection">the database connection to use</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and is parameterized.")]
		private static void InsertItem(IItemInstance item, LoadOptions opt, SqliteConnection connection)
		{
			using (var writeData = new SqliteCommand(Resources.ItemInsertItemSql, connection))
			{
				// new items may not have an id assigned at this point.
				if (item.Id == null || item.Id == Guid.Empty)
				{
					item.Id = Guid.NewGuid();
					item.Version = 1; // default to 1 for new items.
				}
				writeData.Parameters.Add("@itemId", item.Id.ToString().ToUpperInvariant());
				writeData.Parameters.Add("@parentId", item.ParentId == Guid.Empty ? (object)DBNull.Value : item.ParentId.ToString().ToUpperInvariant());
				writeData.Parameters.Add("@externalId", !opt.IsSet(LoadOptions.ExternalId) || string.IsNullOrEmpty(item.ExternalId) ? DBNull.Value : (object)item.ExternalId);
				// TODO: if not given [if parent Id is not null pull from parent, else fail], else use what is given
				writeData.Parameters.Add("@itemClaimId", !opt.IsSet(LoadOptions.ItemClaimId) || item.ItemClaimId == Guid.Empty ? DBNull.Value : (object)item.ItemClaimId.ToString().ToUpperInvariant());
				var parentPath = GetItemHref(item.ParentId, connection);
				var href = parentPath == null ? "col://" + item.Name : parentPath;
				writeData.Parameters.Add("@href", href);
				writeData.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// Inserts the item's relationships
		/// </summary>
		/// <remarks>The connection given should have a current transaction in place.</remarks>
		/// <param name="item">the item to have it's relationships persisted</param>
		/// <param name="opt"></param>
		/// <param name="connection">the sql database connection to use.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and is parameterized.")]
		private static void InsertItemRelationships(IItemInstance item, LoadOptions opt, SqliteConnection connection)
		{
			if (opt.IsNotSet(LoadOptions.Relationships))
				return;
			using (var writeData = new SqliteCommand(Resources.ItemInsertItemRelationshipSql, connection))
			{
				foreach (var relation in item.Relationships)
				{
					writeData.Parameters.Clear();
					writeData.Parameters.Add("@itemId", item.Id.ToString().ToUpperInvariant());
					writeData.Parameters.Add("@version", item.Version);
					writeData.Parameters.Add("@type", relation.RelationshipType);
					writeData.Parameters.Add("@target", relation.TargetItem.ToString());
					writeData.Parameters.Add("@state", relation.State);
					writeData.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Builds a URI for a given item.
		/// </summary>
		/// <param name="id">id of the item to calculate the URI of</param>
		/// <param name="connection">database connection to use for the calculation.</param>
		/// <returns>A uri for the item or null if the item is not found.</returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1305:SpecifyIFormatProvider", MessageId = "System.String.Format(System.String,System.Object,System.Object)"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and is parameterized.")]
		private static string GetItemHref(Guid id, SqliteConnection connection)
		{
			string href = String.Empty;
			using (SqliteCommand command = new SqliteCommand(Resources.ItemSelectNameSql, connection))
			{
				while (id != Guid.Empty)
				{
					command.Parameters.Clear();
					command.Parameters.Add("@id", id.ToString().ToUpperInvariant());
					var reader = command.ExecuteReader();
					if (reader.Read())
					{
						id = reader.IsDBNull(0) ? Guid.Empty : new Guid(reader.GetString(0));
						href = String.Format("{0}/{1}", reader.GetString(1), href);
					}
					reader.Close();
				}
			}
			if (!string.IsNullOrEmpty(href))
				return "col://" + href.TrimEnd('/');
			else
				return null;
		}

		/// <summary>
		/// Updates the item table row.
		/// </summary>
		/// <param name="item">The item to persist.</param>
		/// <param name="opt">The options of which fields should be saved.</param>
		private static void UpdateItem(IItemInstance item, LoadOptions opt, SqliteConnection connection)
		{
			using (var writeData = new SqliteCommand())
			{
				var columnUpdates = new List<string>();

				if (opt.IsSet(LoadOptions.ParentId))
				{
					columnUpdates.Add("ParentId = @parentId");
					writeData.Parameters.Add("@parentId", item.ParentId == Guid.Empty ? (object)DBNull.Value : item.ParentId.ToString().ToUpperInvariant());
				}
				if (opt.IsSet(LoadOptions.ExternalId))
				{
					columnUpdates.Add("ExternalId = @externalId");
					writeData.Parameters.Add("@externalId", string.IsNullOrEmpty(item.ExternalId) ? DBNull.Value : (object)item.ExternalId);
				}
				if (opt.IsSet(LoadOptions.Version) && item.Published)
				{
					columnUpdates.Add("PublishedVersion = @publishedVersion");
					writeData.Parameters.Add("@publishedVersion", item.Version);
				}
				if (opt.IsSet(LoadOptions.ItemClaimId))
				{
					columnUpdates.Add("ItemClaimId = @itemClaimId");
					writeData.Parameters.Add("@itemClaimId", item.ItemClaimId == Guid.Empty ? (object)DBNull.Value : item.ItemClaimId.ToString().ToUpperInvariant());
				}
				if (columnUpdates.Count < 1)
					return;
				string query = string.Format(CultureInfo.InvariantCulture, "UPDATE Item SET {0}  WHERE [Id] = @id", string.Join(", ", columnUpdates.ToArray<string>()));
				writeData.Parameters.Add("@id", item.Id.ToString().ToUpperInvariant());
				writeData.CommandText = query;
				writeData.Connection = connection;
				writeData.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// Updates an item by calling subroutines that save the various parts of an item as needed.
		/// </summary>
		/// <remarks>This method creates the connection and transaction for the update.</remarks>
		/// <param name="item">The item that needs its updates persisted.</param>
		/// <param name="changeCollection">The change collection that tracks which updates should be persisted.</param>
		/// <param name="requestType">Type of the request.</param>
		private void UpdateItemInstance(IItemInstance item, ItemChangeCollection changeCollection)
		{
			using (var connection = new SqliteConnection(m_ConnectionString))
			{
				connection.Open();
				using (var trans = connection.BeginTransaction())
				{
					try
					{
						if (item.Version == 0) // we are not creating a whole new item but we are making a new version
						{
							CopyItemVersionAsNew(item, connection);
						}
						if (changeCollection.ChangeType.HasAnyType(ItemChangeTypes.Hierarchy | ItemChangeTypes.Version | ItemChangeTypes.PropertyAdd | ItemChangeTypes.PropertyDelete | ItemChangeTypes.PropertyModify))
						{
							UpdateItemVersion(item, changeCollection.Options, connection);
							UpdateItem(item, changeCollection.Options, connection);		// update item comes second incase the user is making a new version and publishing it a the same time.
						}
						if (changeCollection.Options.IsSet(LoadOptions.AccessClaims))
						{
							ProcessAccessClaimChanges(item, changeCollection, connection);
						}
						if (changeCollection.Options.IsSet(LoadOptions.Relationships))
						{
							ProcessRelationshipChanges(item, changeCollection, connection);
						}
						trans.Commit();
					}
					catch (Exception)
					{
						trans.Rollback();
						throw;
					}
				}
			}
		}

		/// <summary>
		/// Updates the item version.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="options">The options of which fields to set.</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and a method which generates static sql and is parameterized.")]
		private static void UpdateItemVersion(IItemInstance item, LoadOptions options, SqliteConnection connection)
		{
			using (var writeData = new SqliteCommand())
			{
				var columnUpdates = new StringBuilder();

				if (options.IsSet(LoadOptions.LeftVersion))
				{
					columnUpdates.Append("LeftVersion = @leftVersion, ");
					writeData.Parameters.Add("@leftVersion", !item.LeftVersion.HasValue ? (object)DBNull.Value : item.LeftVersion);
				}
				if (options.IsSet(LoadOptions.RightVersion))
				{
					columnUpdates.Append("RightVersion = @rightVersion, ");
					writeData.Parameters.Add("@rightVersion", !item.RightVersion.HasValue ? (object)DBNull.Value : item.RightVersion);
				}
				if (options.IsSet(LoadOptions.Comment))
				{
					columnUpdates.Append("Comment = @comment, ");
					writeData.Parameters.Add("@comment", string.IsNullOrEmpty(item.Comment) ? (object)DBNull.Value : item.Comment);
				}
				if (options.IsSet(LoadOptions.Name))
				{
					columnUpdates.Append("Name = @name, ");
					writeData.Parameters.Add("@name", item.Name);
				}
				if (options.IsSet(LoadOptions.MimeType))
				{
					columnUpdates.Append("MimeType = @mimeType, ");
					writeData.Parameters.Add("@mimeType", item.MimeType);
				}
				if (options.IsSet(LoadOptions.Specification))
				{
					columnUpdates.Append("SpecificationId = @providerTypeId, ");
					writeData.Parameters.Add("@providerTypeId", item.Specification.ToString().ToUpperInvariant());
				}
				if (options.IsSet(LoadOptions.CreatedBy))
				{
					columnUpdates.Append("CreatedBy = @createdBy, ");
					writeData.Parameters.Add("@createdBy", item.CreatedBy);
				}
				if (options.IsSet(LoadOptions.Properties))
				{
					columnUpdates.Append("PropertyData = @propdata, ");
					writeData.Parameters.Add("@propdata", SerializePropertyData(item.Properties));
				}
				if (columnUpdates.Length < 1 && options.IsNotSet(LoadOptions.LastModified))
					return;
				columnUpdates.Append("LastModified = @lastModified");
				var lMod = !options.IsSet(LoadOptions.LastModified) || item.LastModified == DateTime.MinValue ? DateTime.Now : item.LastModified;
				item.LastModified = lMod; // push back so the returned saved item is correct.
				writeData.Parameters.Add("@lastModified", lMod);

				string query = string.Format(CultureInfo.InvariantCulture, "UPDATE ItemVersion SET {0} WHERE ItemId = @itemId AND Version = @version", columnUpdates.ToString());
				writeData.Parameters.Add("@itemId", item.Id.ToString().ToUpperInvariant());
				writeData.Parameters.Add("@version", item.Version);
				writeData.CommandText = query;

				writeData.Connection = connection;
				writeData.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// Processes the relationship changes.
		/// </summary>
		/// <remarks>given sqlite connection should have an active transaction started before calling.</remarks>
		/// <param name="item">The item.</param>
		/// <param name="changeCollection">The change collection.</param>
		/// <param name="connection">sqlite database connection</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and is parameterized.")]
		private static void ProcessRelationshipChanges(IItemInstance item, ItemChangeCollection changeCollection, SqliteConnection connection)
		{
			using (var insert = new SqliteCommand(Resources.ItemInsertItemRelationshipSql, connection))
			{
				foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.RelationshipAdd))
				{
					IItemRelationship rel = (IItemRelationship)change.CurrentValue;
					insert.Parameters.Clear();
					insert.Parameters.Add("@itemId", item.Id.ToString().ToUpperInvariant());
					insert.Parameters.Add("@version", item.Version);
					insert.Parameters.Add("@type", rel.RelationshipType);
					insert.Parameters.Add("@target", rel.TargetItem.ToString());
					insert.Parameters.Add("@state", rel.State);
					insert.ExecuteNonQuery();
				}
			}
			using (var delete = new SqliteCommand(Resources.ItemDeleteItemRelationshipSql, connection))
			{
				foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.RelationshipDelete))
				{
					IItemRelationship rel = (IItemRelationship)change.PreviousValue;
					delete.Parameters.Clear();
					delete.Parameters.Add("@itemId", item.Id.ToString().ToUpperInvariant());
					delete.Parameters.Add("@version", item.Version);
					delete.Parameters.Add("@type", rel.RelationshipType);
					delete.ExecuteNonQuery();
				}
			}
			using (var update = new SqliteCommand(Resources.ItemUpdateItemRelationshipSql, connection))
			{
				foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.RelationshipModify))
				{
					IItemRelationship rel = (IItemRelationship)change.GetChangeSource(item);
					update.Parameters.Clear();
					update.Parameters.Add("@itemId", item.Id.ToString().ToUpperInvariant());
					update.Parameters.Add("@version", item.Version);
					update.Parameters.Add("@type", rel.RelationshipType);
					update.Parameters.Add("@target", rel.TargetItem.ToString());
					update.Parameters.Add("@state", rel.State);
					update.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Processes the access claim changes.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="changeCollection">The change collection.</param>
		/// <param name="connection">sqlite database connection</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and is parameterized.")]
		private static void ProcessAccessClaimChanges(IItemInstance item, ItemChangeCollection changeCollection, SqliteConnection connection)
		{
			using (var insert = new SqliteCommand(Resources.ItemInsertItemClaimSql, connection))
			{
				foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.PermissionAdd))
				{
					IItemClaim claim = (IItemClaim)change.CurrentValue;
					insert.Parameters.Clear();
					insert.Parameters.Add("@id", claim.Id.ToString().ToUpperInvariant());
					insert.Parameters.Add("@type", claim.ClaimType);
					insert.Parameters.Add("@value", claim.ClaimValue);
					insert.Parameters.Add("@rights", claim.Rights);
					insert.ExecuteNonQuery();
				}
			}
			using (var delete = new SqliteCommand(Resources.ItemDeleteItemClaimSql, connection))
			{
				foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.PermissionDelete))
				{
					IItemClaim claim = (IItemClaim)change.PreviousValue;
					delete.Parameters.Clear();
					delete.Parameters.Add("@id", claim.Id.ToString().ToUpperInvariant());
					delete.Parameters.Add("@type", claim.ClaimType);
					delete.Parameters.Add("@value", claim.ClaimValue);
					delete.ExecuteNonQuery();
				}
			}
			using (var update = new SqliteCommand(Resources.ItemUpdateItemClaimSql, connection))
			{
				foreach (ItemChange change in changeCollection.Changes.Where(p => p.ChangeType == ItemChangeTypes.PermissionModify))
				{
					IItemClaim claim = (IItemClaim)change.GetChangeSource(item);
					update.Parameters.Clear();
					update.Parameters.Add("@id", claim.Id.ToString().ToUpperInvariant());
					update.Parameters.Add("@type", claim.ClaimType);
					update.Parameters.Add("@value", claim.ClaimValue);
					update.Parameters.Add("@rights", claim.Rights);
					update.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Copies the item's leftversion into a new version and updates the item.Version number to reflect the newly created version.
		/// </summary>
		/// <param name="item">The item to copy the leftversion into a new version on.</param>
		private void CopyItemVersionAsNew(IItemInstance item, SqliteConnection connection)
		{
			IItemInstance sourceItem = GetItem(item.Id, item.LeftVersion.Value, LoadOptions.ItemComplete ^ LoadOptions.AccessClaims);
			if (sourceItem == null)
				throw new InvalidOperationException("Left Version refers to a source item which does not exist.  Can not derive a new version.");

			sourceItem = sourceItem.ToStruct(); // convert to a value type
			sourceItem.Version = 0; // override our source item so that a new version is created.
			item.Version = InsertItemVersion(sourceItem, LoadOptions.ItemComplete, connection);
			sourceItem.Version = item.Version;
			InsertItemRelationships(sourceItem, LoadOptions.ItemComplete, connection);
		}

		/// <summary>
		/// Inserts the item version.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="options">The options of which fields to set.</param>
		/// <param name="connection">The database connection to use</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and is parameterized.")]
		private static int InsertItemVersion(IItemInstance item, LoadOptions options, SqliteConnection connection)
		{
			int insertedVersion = 0;
			using (var writeData = new SqliteCommand(Resources.ItemInsertItemVersionSql, connection))
			{
				writeData.Parameters.Add("@itemId", item.Id.ToString().ToUpperInvariant());
				writeData.Parameters.Add("@leftVersion", !options.IsSet(LoadOptions.LeftVersion) || item.LeftVersion == null ? (object)DBNull.Value : item.LeftVersion);
				writeData.Parameters.Add("@rightVersion", !options.IsSet(LoadOptions.RightVersion) || item.RightVersion == null ? (object)DBNull.Value : item.RightVersion);
				writeData.Parameters.Add("@comment", !options.IsSet(LoadOptions.Comment) || string.IsNullOrEmpty(item.Comment) ? (object)DBNull.Value : item.Comment);
				writeData.Parameters.Add("@name", item.Name);
				writeData.Parameters.Add("@mimeType", item.MimeType);
				writeData.Parameters.Add("@specificationId", item.Specification.ToString().ToUpperInvariant());
				writeData.Parameters.Add("@createdBy", item.CreatedBy);
				writeData.Parameters.Add("@lastModified", !options.IsSet(LoadOptions.LastModified) ? DateTime.Now : item.LastModified);
				writeData.Parameters.Add("@propertyData", SerializePropertyData(item.Properties));
				var reader = writeData.ExecuteReader(); // WTF?  Execute Scalar does not seem to work but this does.  Probably a bug in the SqliteCommand implementation.
				if (reader.Read())
					insertedVersion = reader.GetInt32(0);
				else
					throw new InvalidDataException("Could not determine the version data of the item version that was inserted.");
			}
			return insertedVersion;
		}

		/// <summary>
		/// Inserts the item claim.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="options">The options of which fields to set.</param>
		/// <param name="connection">The database connection to use</param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and is parameterized.")]
		private static void InsertItemClaim(IItemInstance item, LoadOptions opt, SqliteConnection connection)
		{
			if (opt.IsNotSet(LoadOptions.Relationships))
				return;
			using (var writeData = new SqliteCommand(Resources.ItemInsertItemClaimSql, connection))
			{
				foreach (var claim in item.AccessClaims)
				{
					if (item.ItemClaimId == claim.Id)
					{
						writeData.Parameters.Clear();
						writeData.Parameters.Add("@id", item.Id.ToString().ToUpperInvariant());
						writeData.Parameters.Add("@type", claim.ClaimType);
						writeData.Parameters.Add("@value", claim.ClaimValue);
						writeData.Parameters.Add("@rights", claim.Rights);
						writeData.ExecuteNonQuery();
					}
				}
			}
		}

		#region select item function methods
		/// <summary>
		/// Works with select commands on item joined itemversion statements to pull returned data
		/// into an item instance.
		/// </summary>
		/// <param name="command">SQL Command to execute</param>
		/// <param name="opt">load options for the item instance(s)</param>
		/// <returns>an enumerable list of item instances</returns>
		private IEnumerable<IItemInstance> GetItemsFromDatabase(SqliteCommand command, LoadOptions opt)
		{
			command.Connection.Open();
			using (var reader = command.ExecuteReader())
			{
				do
				{
					while (reader.Read())
					{
						var newItem = new SqliteItemInstance();
						for (int i = 0; i < reader.FieldCount; i++)
						{
							MapItemProperty(reader, newItem, i);
						}
						if (opt.IsSet(LoadOptions.AccessClaims))
							newItem.AccessClaims = FillItemAccessClaim(newItem, opt, command.Connection);
						if (opt.IsSet(LoadOptions.Relationships))
							newItem.Relationships = FillItemRelationship(newItem, opt, command.Connection);
						yield return newItem;
					}
				} while (reader.NextResult());
			}
			command.Connection.Close();
		}

		/// <summary>
		/// Fills an Item Instnance's Relationships enumberable with the associated relationships.
		/// </summary>
		/// <param name="newItem">the item to fill the relationships of</param>
		/// <param name="opt">loadoptions for which data to load (must include id and version)</param>
		/// <returns></returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and is parameterized.")]
		private IEnumerable<IItemRelationship> FillItemRelationship(SqliteItemInstance newItem, LoadOptions opt, SqliteConnection connection)
		{
			if (!opt.IsSet(LoadOptions.Version | LoadOptions.Id))
				throw new ArgumentException("Loading Item Relationships requires that Item Id and Item Version are loaded as well.", "opt");

			using (var readData = new SqliteCommand(Resources.ItemRelationshipFillSql, connection))
			{
				readData.Parameters.Add("@id", newItem.Id.ToString().ToUpperInvariant());
				readData.Parameters.Add("@version", newItem.Version);
				if (connection.State != ConnectionState.Open)
					connection.Open();
				using (var reader = readData.ExecuteReader())
				{
					while (reader.Read())
					{
						ItemRelationship relation = new ItemRelationship();
						relation.State = reader.IsDBNull(reader.GetOrdinal("State")) ? null : reader.GetString(reader.GetOrdinal("State"));
						relation.TargetItem = new Uri(reader.GetString(reader.GetOrdinal("TargetItem")));
						relation.RelationshipType = reader.GetString(reader.GetOrdinal("RelationshipType"));
						yield return relation;
					}
					reader.Close();
				}
			}
		}

		/// <summary>
		/// Fills an Item Instances's AccessClaims enumerable with the associated claims.
		/// </summary>
		/// <param name="newItem">The item to have the claims filled on</param>
		/// <param name="opt">load options for the item which must include the item id</param>
		/// <returns>an item with a reference to have the claims filled</returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "SQL comes from a resource file and is parameterized.")]
		private IEnumerable<IItemClaim> FillItemAccessClaim(SqliteItemInstance newItem, LoadOptions opt, SqliteConnection connection)
		{
			if (!opt.IsSet(LoadOptions.ItemClaimId))
				throw new ArgumentException("Loading Item AccessClaims requires that Item Security Hash to be loaded.", "opt");
			using (var readData = new SqliteCommand(Resources.ItemClaimFillSql, connection))
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();
				readData.Parameters.Add("@id", newItem.ItemClaimId.ToString().ToUpperInvariant());
				using (var reader = readData.ExecuteReader())
				{
					while (reader.Read())
					{
						ItemClaim claim = new ItemClaim();
						claim.Id = newItem.ItemClaimId;
						claim.ClaimType = reader.GetString(reader.GetOrdinal("ClaimType"));
						claim.ClaimValue = reader.GetString(reader.GetOrdinal("ClaimValue"));
						claim.Rights = (AccessRights)reader.GetInt32(reader.GetOrdinal("AccessFlags"));
						yield return claim;
					}
					reader.Close();
				}
			}
		}
		#endregion

		/// <summary>
		/// Maps the item properties to the values in the data reader.
		/// </summary>
		/// <param name="reader">The data reader that pulls information from the database.</param>
		/// <param name="item">The item to load the data reader values into.</param>
		/// <param name="i">The index of the column within the data reader.</param>
		private static void MapItemProperty(SqliteDataReader reader, IItemInstance item, int i)
		{
			if (!reader.IsDBNull(i))
			{
				switch (reader.GetName(i))
				{
					case "Id": item.Id = new Guid(reader.GetString(i)); break;
					case "Comment": item.Comment = reader.GetString(i); break;
					case "CreatedBy": item.CreatedBy = reader.GetString(i); break;
					case "ExternalId": item.ExternalId = reader.GetString(i); break;
					case "LastModified": item.LastModified = reader.GetDateTime(i); break;
					case "LeftVersion": item.LeftVersion = reader.GetInt32(i); break;
					case "MimeType": item.MimeType = reader.GetString(i); break;
					case "Name": item.Name = reader.GetString(i); break;
					case "ParentId": item.ParentId = new Guid(reader.GetString(i)); break;
					case "Published": item.Published = reader.GetBoolean(i); break;
					case "SpecificationId": item.Specification = new Guid(reader.GetString(i)); break;
					case "RightVersion": item.RightVersion = reader.GetInt32(i); break;
					case "Version": item.Version = reader.GetInt32(i); break;
					case "Href": item.Href = new Uri(reader.GetString(i)); break;
					case "ItemClaimId": item.ItemClaimId = new Guid(reader.GetString(i)); break;
					case "PropertyData": item.Properties = DeserializeProperties((byte[])reader.GetValue(i)); break;
				}
			}
		}

		/// <summary>
		/// Serializes the property data.
		/// </summary>
		/// <param name="properties">The properties.</param>
		/// <returns>a binary serialized List of ItemProperty</returns>
		private static byte[] SerializePropertyData(IEnumerable<IItemProperty> properties)
		{
			IFormatter formatter = new BinaryFormatter();
			using (MemoryStream ms = new MemoryStream())
			{
				formatter.Serialize(ms, properties.ToStruct().ToList());
				return ms.GetBuffer();
			}
		}

		/// <summary>
		/// Deserializes the properties.
		/// </summary>
		/// <param name="buffer">The buffer.</param>
		/// <returns>a list of item properties</returns>
		private static IEnumerable<MetaCollection.Framework.Model.IItemProperty> DeserializeProperties(byte[] buffer)
		{
			if (buffer == null || buffer.Length == 0)
				return null;
			IFormatter formatter = new BinaryFormatter();
			using (var ms = new MemoryStream(buffer))
			{
				List<ItemProperty> props = (List<ItemProperty>)formatter.Deserialize(ms);
				return props.Select(p => (IItemProperty)p);
			}
		}

		/// <summary>
		/// Gets the columns to select given a set of load options.
		/// </summary>
		/// <param name="options">The options.</param>
		/// <returns></returns>
		private static string GetSelectColumns(LoadOptions options)
		{
			LoadOptions columns = options;
			// clear out all of the options that do not map to columns.
			string colNames = columns.Clear(LoadOptions.Properties | LoadOptions.Relationships | LoadOptions.AccessClaims | LoadOptions.Published | LoadOptions.Specification | LoadOptions.ItemClaimId).ToString();

			// if they want published that is a special calculated column
			if (options.IsSet(LoadOptions.Published))
				colNames = String.Join(", ", new string[] { colNames, "(CASE WHEN Item.PublishedVersion = ItemVersion.Version THEN 1 ELSE 0 END) as Published" });

			if (options.IsSet(LoadOptions.Specification))
				colNames = String.Join(", ", new string[] { colNames, "SpecificationId" });

			if (options.IsSet(LoadOptions.Properties))
				colNames = String.Join(", ", new string[] { colNames, "PropertyData" });

			if (options.IsSet(LoadOptions.ItemClaimId))
				colNames = String.Join(", ", new string[] { colNames, "ItemClaimId" });

			return colNames;
		}

		#region IDisposable Members

		public void Dispose()
		{
			if (m_SqlConn != null)
				m_SqlConn.Dispose();
		}

		#endregion
	}
}
